package dbLayer;

import modelLayer.Room;

import java.util.List;
import java.util.ArrayList;
import java.sql.*;

public class DBRoom {
	
	private Connection con;
	
	public DBRoom () {
		this.con = DBConnection.getInstance().getDBcon();
	}
	
	public int highestID () {
		
		int highestID = 0;
		
		ResultSet results;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT MAX(ID) FROM Rooms");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				highestID = results.getInt(1);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
		return highestID;
		
	}
	
	public Room findRoomID (int ID) {
		
		ResultSet results;
		Room room = null;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Rooms WHERE ID = ?");
			statement.setInt(1, ID);
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				room = buildObject(results);
			}
			
			statement.close();
		}
		catch (Exception e) {
			System.out.println("Query exception: "+e);
		}
		
		return room;
	}
	
	public List<Room> findRoomsName (String name) {
		
		ResultSet results;
		List<Room> matchingRooms = new ArrayList<>();
		
		try {
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Rooms WHERE Name like ?");
			statement.setString(1, "%" + name + "%");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			while (results.next()) {
				Room room = new Room();
				room = buildObject(results);
				System.out.println(room);
				matchingRooms.add(room);
			}
			statement.close();
		}
		catch (Exception e) {
		System.out.println("Query exception: "+e);
		}
		
		return matchingRooms;
	}
	
	public List<Room> allRooms () {
		
		List<Room> allRooms = new ArrayList<>();
		ResultSet results;
		
		try {
			
			PreparedStatement statement = con.prepareStatement("SELECT * FROM Rooms");
			statement.setQueryTimeout(5);
			results = statement.executeQuery();
			
			while (results.next()) {
				
				Room room = new Room();
				room = buildObject(results);
				allRooms.add(room);
				
			}
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
		return allRooms;
		
	}
	
	public void addRoom (Room room) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("INSERT INTO Rooms VALUES (?)");
			statement.setString(1, room.getName());
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}
	
	public void updateRoom (Room room) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("UPDATE Rooms SET Name = ? WHERE ID = ?");
			statement.setString(1, room.getName());
			statement.setInt(2, room.getID());
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}

	public void removeRoom(int roomID) {
		
		try {
			
			PreparedStatement statement = con.prepareStatement("DELETE FROM Rooms WHERE ID = ?");
			statement.setInt(1, roomID);
			statement.setQueryTimeout(5);
			
			int resultCount = statement.executeUpdate();
			
			statement.close();
			
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
		
	}

	private Room buildObject(ResultSet results) {
		
		Room room = null;
		
		try {
			room = new Room (results.getInt("ID"),
									results.getString("Name"));
		}
		catch (Exception e) {
			System.out.println("Creation of the subject caused exception: " + e);
		}
		
		return room;
	}
	
}
